Retail Transaction Data Analysis - Feature Engineering

1 Feature engineering

Here, you are going to create features from a very simple dataset: retail transaction data from Kaggle. The dataset provides the customer ID, date of the transaction and transaction amount as shown in the table below. Although this may look like a very simple dataset, you will build a wide range of features. The features will then be used as inputs in several models in upcoming assignments, in which you will try to predict the client’s response to a promotion campaign.

Screen%20Shot%202022-11-11%20at%2013.57.26.png

1.1 Import the data and create the anchor date columns

In order to create features, you need to create some anchor dates. The most typical for transaction data is the end of the month and the year.

  1. Import the dataset as txn and identify the number of rows.

    Answer: The number of rows is 125000.

  1. The date-format in column 'trans_date' is not standard. Create a new column 'txn_date' from 'trans_date' with pd.to_datetime and drop the column 'trans_date'.

  1. Identify the min() and max() of column 'txn_date'.

    Answer: The min() of column 'txn_date' is 2011-05-16 00:00:00 and the max() of column 'txn_date' is 2015-03-16 00:00:00.

  1. Create the column 'ME_DT': the last day of the month in the 'trans_date' column. DateOffset objects is a simple way to do this in pandas.
  1. Create the column 'YEAR': the year in the 'trans_date' column. DatetimeIndex with attribute .year will help you do so.

The table output should look like the snapshot below. Make sure that the column 'ME_DT' works as expected. E.g. for the first line 'trans_date': 2018-08-31 is converted to 2018-08-31. A common mistake in implementing the DateOffset transformation is to convert 2018-08-31 to 2018-09-30 (a date that falls on the last day of a month is converted to the last day of the next month!!!).

Screen%20Shot%202022-11-11%20at%2013.59.14.png

1.2 Create features that capture annual spending

Here the approach is to capture the client’s annual spending. The rationale behind this approach is that the clients spend is not very frequent to capture in a monthly aggregation.

  1. Using groupby and NamedAgg create clnt_annual_aggregations, the annual aggregations dataframe: with sum, mean, std, var, sem, max, min, count as the aggregation functions. A snapshot of the output table is shown below. Notice that the output is a typical MultiIndex pandas dataframe.

Screen%20Shot%202022-11-11%20at%2014.04.23.png

  1. Plot the histogram of the sum and count.

  1. Reset the index and reshape the table with the pivot_table function to create the clnt_annual_aggregations_pivot table shown below with 40 columns (why 40?).

    You should expect columns with NaN values. Impute the NaN entries when you perform the pivot table function and explain your choice of values.

    Answer: There are 40 columns because there are 8 aggregation parameters in the first dataframe and 5 years in the second dataframe. After pivoting, there should be 8x5=40 columns in total. I filled the NaN entries with the value of 0. The NaN values are due to customer only have one or less transaction in a year, then there are no enough samples to compute the aggregation parameters (std, var, sem). Therefore, I choose to use the value of 0 to replace the NaN values.

Screen%20Shot%202022-11-11%20at%2014.06.57.png

  1. The pivoted object you created is a MultiIndex object with hierarchical indexes. You can see the first level (i.e. 0) in the snapshot above with names 'ann_txn_amt_ave', 'ann_txn_amt_max' (and more as indicated by the ...) and the second level (i.e. 1) with names '2011', '2012', etc. You can confirm the multiple levels of the columns with the following two expressions.

    What are your observations regarding the number of levels and the column names?

    Answer: There are two levels and the columns names are tuples made up of these two levels, and the number of columns is the multiplication of number of elements of the first level and the number of elements of the second level.

    clnt_annual_aggregations_pivot.columns.nlevels

    clnt_annual_aggregations_pivot.columns

  1. Finally, you want to save the dataframe clnt_annual_aggregations_pivot as an .xlsx file for future use in the machine learning assignment. To do so, you want to remove the two levels in columns and create a single level with column names: 'ann_txn_amt_ave_2011', 'ann_txn_amt_ave_2012', etc. To do so, use the code snippet below prior to saving the dataframe as an Excel file.

    level_0 = clnt_annual_aggregations_pivot.columns.get_level_values(0).astype(str)

    level_1 = clnt_annual_aggregations_pivot.columns.get_level_values(1).astype(str)

    clnt_annual_aggregations_pivot.columns = level_0 + '_' + level_1

    Describe what each line of code in the box does and save the output dataframe as an Excel file annual_features.xlsx. A snapshot of the desired final output is shown below.

    Answer: The first two lines of code extract column names from the two levels respectively, and the third line of code is to concatenate the column names extracted from the two levels and assign them to the column names of the pivot table.

Screen%20Shot%202022-11-11%20at%2014.11.25.png

  1. What are the possible disadvantages in capturing client transaction behavior with the annual features described in this section (if any)?

    Answer: The possible disadvantages are clinet's transaction behavior may be seasonal which may not be reflected from the annual features.

1.3 Create monthly aggregations

Here, you want to explore the monthly sum of amounts and count of clients transactions.

  1. Create the dataframe that captures the monthly sum and count of transactions per client (name it clnt_monthly_aggregations). Use the groupby function with the Named Aggregation feature which was introduced in pandas version 0.25.0. Make sure that you name the columns as shown in the figure sample. Screen%20Shot%202022-11-11%20at%2014.19.34.png

  1. Create a histogram of both columns you created. What are your observations? What are the most common and maximum values for each column? How do they compare with the ones in section 1.2?

    Answer:

    • My observations are that most of clients spend approximately 30-100 and have 1 transaction monthly.

    • For the monthly aggregation, the most common vlaue is 77 and the maximum value is 460 for 'mth_txn_amt_sum' column, and for 'mth_txn_cnt' column, the most common vlaue is 1 and maximum value is 6.

    • For the annual aggregation, the most common vlaue is 97 and the maximum value is 1317 for 'ann_txn_amt_sum' column, and for 'ann_txn_cnt' column, the most common vlaue is 3 and maximum value is 8.

    • For both transaction sum and count columns, the values are higher from annual aggregation than those from monthly aggregation. However, this is not a linearly relationship. Therefore, it is necessary to investigate clinet's monthly behaviour rather than annual behaviour.

      The output dataframe should look like the snapshot shown on the right for client with ID CS1112 (confirm this with slicing your output dataframe).

      Most clients in this dataset shop a few times a year. For example, the client with ’customer_id’ CS1112 shown here made purchases in 15 out of 47 months of data in the txn table. The information in this dataset is ”irregular”; some clients may have an entry for a month, while others do not have an entry (e.g. when they don’t shop for this particular month).

1.4 Create the base table for the rolling window features

In order to create the rolling window features (more on this in the next section), you need to create a base table with all possible combinations of 'customer_id' and 'ME_DT'. For example, customer CS1112 should have 47 entries, one for each month, in which 15 will have the value of transaction amount and the rest 32 will have zero value for transaction amount. This will essentially help you convert the "irregular" clnt_monthly_aggregations table into a "regular" one.

  1. Create the numpy array of the unique elements in columns 'customer_id' and 'ME_DT' of the txn table you created in section 1.1. Confirm that you have 6,889 unique clients and 47 unique month-end-dates.

  1. Use itertools.product to generate all the possible combinations of 'customer_id' and 'ME_DT'. Itertools is a Python module that iterates over data in a computationally efficient way. You can perform the same task with a for-loop, but the execution may be inefficient. For a brief overview of the Itertools module see here. If you named the numpy arrays with the unique elements: clnt_no and me_dt, then the code below will create an itertools.product object (you can confirm this by running: type(base table)).

    from itertools import product

    base_table = product(clnt_no, me_dt)

  1. Next, you want to convert the itertools.product object base_table into a pandas object called base_table_pd. To do so, use pd.DataFrame.from_records and name the columns 'CLNT_NO' and 'ME_DT'.

  1. Finally, you want to validate that you created the table you originally wanted. There are two checks you want to perform:

    • Filter client CS1112 and confirm that the dates fall between the min and max month-dates you identified in section 1.1. Also, confirm that the snapshot of client CS1112 has 47 rows, one for each month in the dataset.

    • Confirm that the base_table_pd has 323,783 rows, which is the expected value of combinations for 6,889 unique clients and 47 unique month-end dates.

1.5 Create the monthly rolling window features

With the base_table_pd as a starting point you can convert the irregular transaction data into the typical time series data; data captured at equal intervals. Feature engineering of time series data gives you the potential to build very powerful predictive models.

  1. Left-join the base_table_pd with the clnt_monthly_aggregations table from section 1.3 on [CLNT NO, ME DT] to create the table base_clnt_mth. Comment on the following questions in Markdown:

    • Why do some rows have NaN values? Answer: Because the customer does not have transaction in that month.
    • What values will you choose to impute NaN values in the sum and count columns? Perform the imputation you suggest. Answer: I will choose to fill NaN values with the value of 0 as the customer does not make any purchase.
    • Confirm that the number of rows is what you expect. What is the value? Answer: The number of rows is 323783, which is the same as the base_table_pd.
    • How are tables base_clnt_mth and clnt_monthly_aggregations different? Comment on the number of rows and the content of each table. Answer: The table of clnt_monthly_aggregations has 103,234 rows, and the table of base_clnt_mth has 323,783 rows. The base_clnt_mth table is the result of the combination of 6,889 unique clients and 47 unique month-end-dates, including the entries that the customer doesn't have any transactions in certain month. However, the clnt_monthly_aggregations only contain the entires that the customer have transaction record. Therefore, the rows of clnt_monthly_aggregations is much less than base_clnt_mth.
  1. For the next step, the calculation of the rolling window features, you need to sort the data first by 'CLNT_NO' and then by 'ME_DT' in ascending order. This is necessary to create the order for rolling windows, e.g. 2011-05-31, 2011-06-30, etc.

  1. The idea behind rolling window features is captured in the image below. You calculate some statistical properties (e.g. average) based on a window that is sliding. In the image below, the window is 7 which means that the last 7 points are used at every row to calculate the statistical property. Screen%20Shot%202022-11-11%20at%2014.23.54.png

    Here, you have to calculate separately the 3, 6 and 12-month rolling window features (tables: rolling_features_3M, rolling_features_6M, rolling_features_12M) for every client that calculates the aggregations 'sum', 'mean' and 'max' for both columns 'mth_txn_amt_sum' and 'mth_txn_cnt'. The steps to achieve this with base_clnt_mth as the starting dataframe are:

    • groupby the client number
    • select the two columns you want to aggregate
    • use the rolling function with the appropriate windows
    • aggregate with 'sum', 'mean' and 'max'

The output of the 3-month rolling window dataframe is shown below. Also, answer the following questions in the .ipynb notebook as Markdown comments.

Screen%20Shot%202022-11-11%20at%2014.25.24.png

  1. Merge the 4 tables: base_clnt_mth, rolling_features_3M, rolling_features_6M, rolling_features_12M in the output all_rolling_features. It is recommended to drop the level: 0 of the rolling features MultiIndex table and join with base_clnt_mth on the indexes.

    Make sure you understand why joining on the indexes preserves the CLNT NO and ME DT for each index.

  1. Confirm that your final output all_rolling_features has 323,783 rows and 22 columns and save it as mth_rolling_features.xlsx.

In this section, you will create the date-related features that capture information about the day of the week the transactions were performed.

  1. The DatetimeIndex object you used earlier allows you to extract many components of a DateTime object. Here, you want to use the attributes dt.dayofweek and/or dt.day_name() to extract the day of the week from column 'txn_date' of the txn table (with Monday=0, Sunday=6). The expected output below shows both columns.

Screen%20Shot%202022-11-11%20at%2014.32.23.png

  1. Create the bar plot that shows the count of transactions per day of the week.
  1. Following the same logic as in section 1.2, generate the features that capture the count of transactions per client, year and day of the week. The intermediate MultiIndex dataframe (with nlevels=3) and the final pivoted output with a single index are shown in the snapshots below.

Screen%20Shot%202022-11-11%20at%2014.33.26.png

  1. Confirm that your output has the same number of rows as the final output in section 1.2 and save it as annual_day_of_week_counts_pivot.xlsx. How many features/columns did you create in this section?
  1. Similarly, generate the features that capture the count of transactions per client, month-end-date and day of the week. In contrast with the annual pivot table in the previous step, here you want to create the pivot with ['customer_id', 'ME_DT'] as index to obtain the following output dataframe.

Screen%20Shot%202022-11-11%20at%2014.34.55.png

  1. Join with base_table_pd as you did in section 1.5 and impute with your choice of value for NaN. Save the final output as mth_day_counts.xlxs.

    Answer: Fill the NaN values with the value of 0 as the customer does not have any transaction on certain day of a week.

In this date-related features set, you want to capture the frequency of the transactions in terms of the days since the last transaction. This set of features applies only to the monthly features.

  1. The starting point is again the txn table. Recall that most clients have a single purchase per month, but some clients have multiple purchases in a month. Since you want to calculate the "days since last transaction", you want to capture the last transaction in a month for every client.

    Use the appropriate groupby to create the table last_monthly_purchase that captures the last 'txn_date' (aggfunc=max) for every client and month.

  1. Join base_table_pd with last_monthly_purchase as you did in section 1.5. The snapshot below shows the output of the created object last_monthly_purchase base for client CS1112 who made her/his first purchase on June 2011, then no purchase on July and made a purchase again on August 2011. What values will you use to impute the NaT values here? NaT stands for "Not a Timestamp".

    Answer: I will use the last monthly purchase values to impute the NaT values here. The last purchase date from previous month would be used to fill the NaT values if the customer doesnn't have transaction histories on certain month. However, we can keep the NaT values for the months that does not have any transaction before.

Screen%20Shot%202022-11-11%20at%2014.38.41.png

  1. To answer the imputation problem, we have to think what value should we use for say July 2011 for 'last_monthly_purchase'? The answer is that in July the value for the last monthly purchase is the previous line value: 2011-06-15. In other words, for every client we want to forward-fill the NaT values.

    While pandas fillna() method has a method to forward-fill, here we want to use the apply and a lambda function with the forward-fill function ffill(), with the following expression: .apply(lambda x: x.ffill()) applied on object last_monthly_purchase base grouped by CLNT_NO. Below, I am showing a snapshot for lines [92:98] that confirm the transition between clients CS1113 and CS1114.

    You can also recreate the forward-fill with the fillna() method, however there is a disadvantage and a reason the .apply() method is preferred here.

Screen%20Shot%202022-11-11%20at%2014.39.38.png

The forward-fill on the grouped by CLNT_NO object is expected to leave NaT values for the first months of every client until they purchase something. The above snapshot confirms that for client CS1114.

  1. Subtract the two date columns and convert the output to .dt.days to calculate the column 'days_since_last_txn' as shown in the following snapshot.

Screen%20Shot%202022-11-11%20at%2014.40.25.png

  1. Plot a histogram of the 'days_since_last_txn'. Based on the values you observe in the histogram, impute the remaining NaN values (i.e. for the initial months before a client makes a purchase). Save the columns ['CLNT_NO', 'ME_DT', 'days_since_last_txn'] as days_since_last_txn.xlsx.

    Answer: In the histogram, I observe that most "days since last transaction" locates at the first bar (a very short period of time that is close to 0). Therefore, I impute the remaining NaN values with 0 for a client does not make a purchase before.